package jp.ac.u_tokyo.dch.dao.schema;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.SchemaGroup;
import jp.ac.u_tokyo.dch.model.db.DataSchema;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
import jp.ac.u_tokyo.dch.view.Constants;

import org.apache.commons.lang.xwork.StringUtils;
/**
* @version 1.1
*/
public class DataSchemaDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public DataSchemaDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}

	public SchemaGroup selectMetaDataPattern(String schemaId) throws DBException {

		SchemaGroup schemaGroup = null;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT " +
					" lv6.schemaid as lv6, lv6.name as lv6Name," +
					" lv5.schemaid as lv5, lv5.name as lv5Name," +
					" lv4.schemaid as lv4, lv4.name as lv4Name," +
					" lv3.schemaid as lv3, lv3.name as lv3Name," +
					" lv2.schemaid as lv2, lv2.name as lv2Name," +
					" lv1.schemaid as lv1, lv1.name as lv1Name FROM" +
					" data_schema as lv6 INNER JOIN data_schema as lv5 " +
					" INNER JOIN data_schema as lv4 " +
					" INNER JOIN data_schema as lv3 " +
					" INNER JOIN data_schema as lv2 " +
					" INNER JOIN data_schema as lv1 " +
					" on lv6.schemaid = ? " +
					" AND lv6.sort = '006' " +
					" AND lv6.oyaschemaid = lv5.schemaid " +
					" AND lv5.oyaschemaid = lv4.schemaid " +
					" AND lv4.oyaschemaid = lv3.schemaid " +
					" AND lv3.oyaschemaid = lv2.schemaid " +
					" AND lv2.oyaschemaid = lv1.schemaid ";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(schemaId));

			rs = ps.executeQuery();

			if (rs.next()){

				schemaGroup = new SchemaGroup();
				schemaGroup.setLv1Schemaid(rs.getString("lv1"));
				schemaGroup.setLv1SchemaName(rs.getString("lv1Name"));
				schemaGroup.setLv2Schemaid(rs.getString("lv2"));
				schemaGroup.setLv2SchemaName(rs.getString("lv2Name"));
				schemaGroup.setLv3Schemaid(rs.getString("lv3"));
				schemaGroup.setLv3SchemaName(rs.getString("lv3Name"));
				schemaGroup.setLv4Schemaid(rs.getString("lv4"));
				schemaGroup.setLv4SchemaName(rs.getString("lv4Name"));
				schemaGroup.setLv5Schemaid(rs.getString("lv5"));
				schemaGroup.setLv5SchemaName(rs.getString("lv5Name"));
				schemaGroup.setLv6Schemaid(rs.getString("lv6"));
				schemaGroup.setLv6SchemaName(rs.getString("lv6Name"));

			}
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}

		return schemaGroup;
	}

	/**
	 * メータデータパターンリストを取得する
	 * @return メータデータパターンリスト
	 * @throws DBException 例外
	 */
	public List<SchemaGroup> selectMetaDataPatternList() throws DBException {

		List<SchemaGroup> list = new ArrayList<SchemaGroup>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT " +
					" lv6.schemaid as lv6, lv6.name as lv6Name," +
					" lv5.schemaid as lv5, lv5.name as lv5Name," +
					" lv4.schemaid as lv4, lv4.name as lv4Name," +
					" lv3.schemaid as lv3, lv3.name as lv3Name," +
					" lv2.schemaid as lv2, lv2.name as lv2Name," +
					" lv1.schemaid as lv1, lv1.name as lv1Name FROM" +
					" data_schema as lv6 INNER JOIN data_schema as lv5 " +
					" INNER JOIN data_schema as lv4 " +
					" INNER JOIN data_schema as lv3 " +
					" INNER JOIN data_schema as lv2 " +
					" INNER JOIN data_schema as lv1 " +
					" on lv6.sort = '006' " +
					" AND lv6.oyaschemaid = lv5.schemaid " +
					" AND lv5.oyaschemaid = lv4.schemaid " +
					" AND lv4.oyaschemaid = lv3.schemaid " +
					" AND lv3.oyaschemaid = lv2.schemaid " +
					" AND lv2.oyaschemaid = lv1.schemaid ";

			ps = dbconn.prepareStatement(query);

			rs = ps.executeQuery();

			while (rs.next()){

				SchemaGroup schemaGroup = new SchemaGroup();
				schemaGroup.setLv1Schemaid(rs.getString("lv1"));
				schemaGroup.setLv1SchemaName(rs.getString("lv1Name"));
				schemaGroup.setLv2Schemaid(rs.getString("lv2"));
				schemaGroup.setLv2SchemaName(rs.getString("lv2Name"));
				schemaGroup.setLv3Schemaid(rs.getString("lv3"));
				schemaGroup.setLv3SchemaName(rs.getString("lv3Name"));
				schemaGroup.setLv4Schemaid(rs.getString("lv4"));
				schemaGroup.setLv4SchemaName(rs.getString("lv4Name"));
				schemaGroup.setLv5Schemaid(rs.getString("lv5"));
				schemaGroup.setLv5SchemaName(rs.getString("lv5Name"));
				schemaGroup.setLv6Schemaid(rs.getString("lv6"));
				schemaGroup.setLv6SchemaName(rs.getString("lv6Name"));

				list.add(schemaGroup);
			}
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * データスキーマリストを取得する
	 * @param sort 分類
	 * @return データスキーマリスト
	 * @throws DBException 例外
	 */
	public List<DataSchema> selectList(String sort) throws DBException {

		List<DataSchema> list = new ArrayList<DataSchema>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM data_schema where sort = ?";

			ps = dbconn.prepareStatement(query);
			ps.setString(1, sort);
			rs = ps.executeQuery();
			while (rs.next()){

				DataSchema dataSchema = new DataSchema();
				dataSchema.setSchemaid(rs.getString("schemaid"));
				dataSchema.setName(rs.getString("name"));
				dataSchema.setSort(rs.getString("sort"));
				dataSchema.setCreateUser(rs.getString("createuser"));
				dataSchema.setCreateDate(rs.getString("createdate"));
				dataSchema.setModifyUser(rs.getString("modifyuser"));
				dataSchema.setModifyDate(rs.getString("modifydate"));

				if (isExistDataBySchemaid(dataSchema.getSchemaid())) {

					dataSchema.setEditSign(Constants.EDIT_SIGN_HUKA);
				} else {
					dataSchema.setEditSign(Constants.EDIT_SIGN_KA);
				}

				list.add(dataSchema);
			}
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}

		return list;
	}

	private boolean isExistDataBySchemaid(String schemaid) throws DBException {

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		boolean isexist = false;
		try{
			String query = "SELECT * FROM data AS main where exists (select * from data where main.id = data.id and schemaid = ?) ";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, Integer.valueOf(schemaid));
			rs = ps.executeQuery();

			isexist = rs.next();

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return isexist;

	}

	/**
	 * データスキーマ情報を取得
	 * @param データスキーマID
	 * @return データスキーマ情報
	 * @throws DBException 例外
	 */
	public DataSchema selectObject(String id) throws DBException {

		DataSchema dataSchema = null;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT nowschema.schemaid, nowschema.name,nowschema.sort,nowschema.createuser," +
					" nowschema.createdate,nowschema.modifyuser,nowschema.modifydate,nowschema.oyaschemaid, oya.name as oyaschemaname " +
					" FROM data_schema as nowschema LEFT OUTER JOIN data_schema as oya on nowschema.oyaschemaid = oya.schemaid" +
					" WHERE nowschema.schemaid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, Integer.valueOf(id));

			rs = ps.executeQuery();

			if (rs.next()){

				dataSchema = new DataSchema();
				dataSchema.setSchemaid(rs.getString("schemaid"));
				dataSchema.setName(rs.getString("name"));
				dataSchema.setSort(rs.getString("sort"));
				dataSchema.setCreateUser(rs.getString("createuser"));
				dataSchema.setCreateDate(rs.getString("createdate"));
				dataSchema.setModifyUser(rs.getString("modifyuser"));
				dataSchema.setModifyDate(rs.getString("modifydate"));
				dataSchema.setOyaschemaid(rs.getString("oyaschemaid"));
				dataSchema.setOyaschemaName(rs.getString("oyaschemaname"));
			}
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataSchema;
	}

	/**
	 * スキーマ情報を挿入する
	 * @param schema スキーマ情報
	 * @return 挿入件数
	 * @throws DBException 例外
	 */
	public int insert(DataSchema schema)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "INSERT INTO data_schema (schemaid,name,sort,createuser,createdate,modifyuser,modifydate,oyaschemaid) VALUES (?,?,?,?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);
			ps.setInt(index++, Integer.valueOf(schema.getSchemaid()));
			ps.setString(index++, schema.getName());
			ps.setString(index++, schema.getSort());
			ps.setString(index++, schema.getCreateUser());
			ps.setString(index++, schema.getCreateDate());
			ps.setString(index++, schema.getModifyUser());
			ps.setString(index++, schema.getModifyDate());
			ps.setString(index++, schema.getOyaschemaid());

			result = ps.executeUpdate();
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * スキーマ情報を削除する
	 * @param schemaId スキーマID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(Integer schemaId)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM data_schema WHERE schemaid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, schemaId);
			result = ps.executeUpdate();
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * スキーマ情報を更新する
	 * @param schema スキーマ情報
	 * @return 更新件数
	 * @throws DBException 例外
	 */
	public int update(DataSchema schema) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "UPDATE data_schema SET name = ?, modifyuser = ?, modifydate = ?, oyaschemaid = ? WHERE schemaid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setString(index++, schema.getName());
			ps.setString(index++, schema.getModifyUser());
			ps.setString(index++, schema.getModifyDate());
			ps.setString(index++, schema.getOyaschemaid());
			ps.setInt(index++, Integer.valueOf(schema.getSchemaid()));
			result = ps.executeUpdate();
			//資料テーブルに現物資料データとのトランザクション処理のためDBコネクションクローズはしない。
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * データ名をチェックする
	 * @param name 名前
	 * @param schemaId データスキーマID
	 * @return true：重複存在、false：存在しない
	 * @throws DBException
	 */
	public boolean checkSchemaName(String sort, String name, String schemaId)throws DBException {

		CustomPreparedStatement pstmt = null;
		CustomResultSet rs = null;
		String sql = "";
		boolean state = false;
		try{
			if(StringUtils.isNotEmpty(name)){
				if(StringUtils.isNotEmpty(schemaId)){
					sql = "select name from data_schema where sort = ? and name = ? and schemaid != ?";
					pstmt = dbconn.prepareStatement(sql);
					pstmt.setString(1, sort);
					pstmt.setString(2, name);
					pstmt.setString(3, schemaId.toString());
				}else{
					sql = "select name from data_schema where sort = ? and name=?";
					pstmt = dbconn.prepareStatement(sql);
					pstmt.setString(1, sort);
					pstmt.setString(2, name);
				}

			}
			rs = pstmt.executeQuery();

			if (rs.next()) {
				state = true;
			} else {
				state = false;
			}
		}catch(DBException e) {
			throw e;
		} finally{
			CommonDBConnection.closeResource(pstmt, rs);

		}
		return state;
	}

}
